package db.member;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

import db.member.*;

public class MemberDAO {
	
	Connection con;
	PreparedStatement pstmt;
	ResultSet rs;
	public MemberDAO(){
		
		try{
			   Context init = new InitialContext();
			   Context envCtx = (Context) init.lookup("java:comp/env");
			   DataSource ds = (DataSource)envCtx.lookup("jdbc/dbcp");
			     con = ds.getConnection();
		}catch(Exception ex){
			System.out.println("접속실패: " + ex);
			return;
		}
	}
	
	public int getmemberCount() {
		int x= 0;
		
		try{
			pstmt=con.prepareStatement("select count(*) from shop_member");
			rs = pstmt.executeQuery();
			
			if(rs.next()){
				x=rs.getInt(1);
			}
		}catch(Exception ex){
			System.out.println("Count ����: " + ex);			
		}finally{
			if(rs!=null) try{rs.close();}catch(SQLException ex){}
			if(pstmt!=null) try{pstmt.close();}catch(SQLException ex){}
		}
		return x;
	}
	
	public List getMemberList(int page,int limit){
		String list_sql="select * from" +
        "(select rownum rnum,shop_id,shop_name,shop_sex," +
        "shop_add01,shop_mail from " +
        "(select * from shop_member order by shop_name asc))"+
		"where rnum>=? and rnum<=?";
		List list = new ArrayList();
		
		
		int startrow=(page-1)*10+1; 
		int endrow=startrow+limit-1; 
		try{
			pstmt = con.prepareStatement(list_sql);
			pstmt.setInt(1, startrow);
			pstmt.setInt(2, endrow);
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				MemberBean member = new MemberBean();
				member.setShop_id(rs.getString("shop_id"));
				member.setShop_name(rs.getString("shop_name"));
				member.setShop_sex(rs.getString("shop_sex"));
				member.setShop_add01(rs.getString("shop_add01"));
				member.setShop_mail(rs.getString("shop_mail"));
				list.add(member);
			}
			
			return list;
		}catch(Exception ex){
			System.out.println("memberlistList���� : " + ex);
		}finally{
			if(rs!=null) try{rs.close();}catch(SQLException ex){}
			if(pstmt!=null) try{pstmt.close();}catch(SQLException ex){}
		}
		return null;
	}
	
	public int empLogin(MemberBean bean){
		String sql="select shop_pswd from shop_member where shop_id=?";
		int result=-1;
		
		try{
			pstmt=con.prepareStatement(sql);
			pstmt.setString(1, bean.getShop_id());
			rs = pstmt.executeQuery();
			
			if(rs.next()){
				if(rs.getString("shop_pswd").equals(
									bean.getShop_pswd())){
					result=1;
				}else{
					result=0;
				}
			}else{
				result=-1;
			}
		}catch(Exception ex){
			System.out.println("isMember: " + ex);			
		}finally{
			if(rs!=null) try{rs.close();}catch(SQLException ex){}
			if(pstmt!=null) try{pstmt.close();}catch(SQLException ex){}
		}
		
		return result;
	}
	
	public void empName(MemberBean bean){
		String sql="select shop_name, shop_pswd from shop_member where shop_id=?";
		
		try{
			pstmt=con.prepareStatement(sql);
			pstmt.setString(1, bean.getShop_id());
			rs = pstmt.executeQuery();
			
			if(rs.next()){
				bean.setShop_name(rs.getString("shop_name"));
				bean.setShop_pswd(rs.getString("shop_pswd"));
			}
		}catch(Exception ex){
			System.out.println("isMember: " + ex);			
		}finally{
			if(rs!=null) try{rs.close();}catch(SQLException ex){}
			if(pstmt!=null) try{pstmt.close();}catch(SQLException ex){}
		}
		
		
	}
	
	public List Zipcode(String don){
		System.out.println("우편번호 나와라!!"+don);
		String zipcode_sql="select *from ziptb where dong like ?";
		List list = new ArrayList();
		System.out.println("post 방식으로 드러옴"+list);
		try{
			pstmt = con.prepareStatement(zipcode_sql);
			pstmt.setString(1, "%"+don+"%");
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				String sido=rs.getString("sido");
				String gugun=rs.getString("gugun");
				String dong=rs.getString("dong");  
				String bunji=rs.getString("bunji");
				if(bunji == null) bunji="";
				System.out.println("rs.next 방식으로 드러옴"+sido);
				System.out.println("rs.next 방식으로 드러옴"+gugun);
				System.out.println("rs.next 방식으로 드러옴"+dong);
				System.out.println("rs.next 방식으로 드러옴"+bunji);
				
				String shop_zipcode=rs.getString("zipcode");
				String addr=sido+ " "+gugun+ " "+dong+ " "+bunji;
				
				list.add(shop_zipcode+","+addr);
			}
			
			return list;
		}catch(Exception ex){
			System.out.println("List: " + ex);
		}finally{
			if(rs!=null) try{rs.close();}catch(SQLException ex){}
			if(pstmt!=null) try{pstmt.close();}catch(SQLException ex){}
		}
		return null;
	}
	
	public int confirmId(String shop_id) throws SQLException{
		String sql="select SHOP_ID from SHOP_MEMBER where SHOP_ID=? ";
		int x=-1;
		System.out.println("문어");
		try{
			pstmt=con.prepareStatement(sql);
			pstmt.setString(1, shop_id);
			rs=pstmt.executeQuery();
			
			if(rs.next()){
				x=1;
			}else{
				x=-1;
			}
			
			return x;
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(pstmt != null){ pstmt.close(); }
			if(rs != null){ rs.close(); }
			
		}
		
		return -1;
	}
	
	public boolean empJoin(MemberBean bean) throws SQLException{
		String sql=null;
		
		try{
			sql="insert into shop_member values "+
				"(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
			
			pstmt=con.prepareStatement(sql);
			pstmt.setString(1, bean.getShop_id());
			pstmt.setString(2, bean.getShop_name());
			pstmt.setString(3, bean.getShop_pswd());
			pstmt.setInt(4, bean.getShop_bir_y());
			pstmt.setInt(5, bean.getShop_bir_m());
			pstmt.setInt(6, bean.getShop_bir_d());
			pstmt.setString(7, bean.getShop_sex());
			pstmt.setString(8, bean.getShop_mail());
			pstmt.setString(9, bean.getShop_mail_service());
			pstmt.setString(10, bean.getShop_phon());
			
			pstmt.setString(11, bean.getShop_zipcode());
			pstmt.setString(12, bean.getShop_add01());
			pstmt.setString(13, bean.getShop_add02());
			pstmt.setTimestamp(14, bean.getShop_date());
			pstmt.executeUpdate();
			
			return true;
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(pstmt != null){ pstmt.close();}

		}
		
		return false;
	}
	
	public int getListCount() {
		int x= 0;
		
		try{
			pstmt=con.prepareStatement("select count(*) from member_ex");
			rs = pstmt.executeQuery();
			
			if(rs.next()){
				x=rs.getInt(1);
			}
		}catch(Exception ex){
			System.out.println("Count: " + ex);			
		}finally{
			if(rs!=null) try{rs.close();}catch(SQLException ex){}
			if(pstmt!=null) try{pstmt.close();}catch(SQLException ex){}
		}
		return x;
	}
	
	public List empList(int page,int limit){
		String emplist="select * from" +
        "(select rownum rnum,id, name, mobile, email from" +
        "(select * from member_ex order by name asc))"+
		"where rnum>=? and rnum<=?";
		
		List list = new ArrayList();
		int startrow=(page-1)*10+1; 
		int endrow=startrow+limit-1; 
		try{
			pstmt = con.prepareStatement(emplist);
			pstmt.setInt(1, startrow);
			pstmt.setInt(2, endrow);
			
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				MemberBean bean = new MemberBean();
				bean.setShop_id(rs.getString("ID"));
				bean.setShop_name(rs.getString("NAME"));
		
				bean.setShop_mail(rs.getString("MAIL"));
				list.add(bean);
			}
			
			return list;
		}catch(Exception ex){
			System.out.println("List: " + ex);
		}finally{
			if(rs!=null) try{rs.close();}catch(SQLException ex){}
			if(pstmt!=null) try{pstmt.close();}catch(SQLException ex){}
		}
		return null;
	}
	
	public MemberBean empDetail(String id) throws Exception{
		MemberBean bean = null;
		try{
			pstmt = con.prepareStatement(
					"select * from member_ex where id = ?");
			pstmt.setString(1, id);
			rs= pstmt.executeQuery();
			
			if(rs.next()){
				bean = new MemberBean();
				bean.setShop_id(rs.getString("ID"));
				bean.setShop_name(rs.getString("NAME"));
				
				bean.setShop_add01(rs.getString("ADD01"));
				bean.setShop_add02(rs.getString("ADD02"));
				bean.setShop_mail(rs.getString("MAIL"));
			}
			return bean;
		}catch(Exception ex){
			System.out.println("getDetail : " + ex);
		}finally{
			if(rs!=null)try{rs.close();}catch(SQLException ex){}
			if(pstmt !=null)try{pstmt.close();}catch(SQLException ex){}
		}
		return null;
	}
	
}
